﻿using System;
using System.Collections.Generic;
using System.Text;
//Thêm các using sau
using DAL; //cần Add reference
using DTO; //cần Add reference
using System.Data;
using System.Data.OleDb;

namespace BLL
{
    public class SinhVienDAL
    {
        /* Có thể lấy tất cả dữ liệu của một bảng như sau
        public DataTable GetAll()
        {
            DataProvider dataprovider = new DataProvider();
            return dataprovider.ExecuteSelectQuery("Select * from SINHVIEN", null);
        }
        */
        public List<SINHVIEN> GetAll()
        {
            SINHVIEN sinhvien;
            List<SINHVIEN> list = new List<SINHVIEN>();
            DataProvider dataprovider = new DataProvider();
            DataTable datatable = new DataTable();
            datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN", null);
            for (int i = 0; i < datatable.Rows.Count; i++)
            {
                sinhvien = new SINHVIEN();
                sinhvien.MaSV = datatable.Rows[i]["MSSV"].ToString();
                sinhvien.Holot = datatable.Rows[i]["Holot"].ToString();
                sinhvien.Ten = datatable.Rows[i]["Ten"].ToString();
                list.Add(sinhvien);
            }
            return list;
        }

        public SINHVIEN GetSINHVIEN(string MSSV)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@MSSV", OleDbType.Char);
            parameters[0].Value = MSSV;
            DataTable datatable = new DataTable();
            datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN where MSSV = @MSSV", parameters);
            SINHVIEN sinhvien = new SINHVIEN();
            if (datatable.Rows.Count != 0)
            {
                sinhvien.MaSV =datatable.Rows[0]["MSSV"].ToString();
                sinhvien.Holot = datatable.Rows[0]["Holot"].ToString();
                sinhvien.Ten = datatable.Rows[0]["Ten"].ToString();
                return sinhvien;
            }
            else
                return null;
        }

        public bool UpdateSINHVIEN(string MSSV, string Holot, string Ten, string MSSVcu)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[4];
            parameters[0] = new OleDbParameter("@MSSV", OleDbType.Char);
            parameters[0].Value = MSSV;
            parameters[1] = new OleDbParameter("@Holot", OleDbType.Char);
            parameters[1].Value = Holot;
            parameters[2] = new OleDbParameter("@Ten", OleDbType.Char);
            parameters[2].Value = Ten;
            parameters[3] = new OleDbParameter("@MSSVcu", OleDbType.Char);
            parameters[3].Value = MSSVcu;
            return dataprovider.ExecuteNonSelectQuery("update SINHVIEN set MSSV=@MSSV, Holot=@Holot, Ten=@Ten where MSSV = @MSSVcu", parameters);
        }
    }
}
